import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure
from project_utils.mysql_conncet import ReadFromMysql
from config import *


def result_gnscgzl(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket, table_name, date)

    # 获取mysql func_data功能信息
    mysql = ReadFromMysql(mysqlhost, user, password, 3306)
    mysql.makepool(database)
    func = mysql.read_from_mysql(f"select * from {func_table} where type = 'FRIDGE' ")
    func_sql = ""
    for row in func:
        name = row['func_name']
        func_sql += f'{name}模式|{name}功能|'
    func_sql = func_sql[0:func_sql.rindex('|')]

    sql = f'''insert into fridge_biz_result.result_gnscgzl
                (
                    select
                    *,format_datetime(cast ('{date}' as date) - interval '1' month, 'YYYY-MM') as time
                    from (
                        select
                            if(model is null,'',model) as model,
                            regexp_replace(func,'功能|模式','') as function_name,
                            sum(count(distinct objectid)) over(partition by if(model is null,'',model)) as total_num,
                            count(1) as fault_num
                        from
                            (
                            select
                                objectid,
                                zzfld00000b as sn,
                                upper(if(t2.model is null,regexp_extract(zzfld000036,'[a-zA-Z0-9]+-[a-zA-Z0-9]+') ,t2.model)) as model,
                                regexp_extract(
                                    description,
                                    '{func_sql}'
                                    ) as func
                            from
                                ods.kyd_ods t1
                            left join
                                dim.material t2
                            on
                                t1.material = t2.material
                            where
                                date(p_date)>=(cast ('{date}' as date) - interval '1' month)
                            and
                                date(p_date) < cast ('{date}' as date)
                            and
                                zzfld000001='QT30'
                            and
                                zzfld00002ft != '安装'
                            ) t
                        group by
                            if(model is null,'',model),regexp_replace(func,'功能|模式','')
                        ) t3
                    where function_name is not null
                )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


def get_lower_case_name(text):
    lst = []
    for index, char in enumerate(text):
        if char.isupper() and index != 0:
            lst.append("_")
        lst.append(char)
    return "".join(lst).lower()


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_gnscgzl(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)
